package com.xxl.job.executor.common.util;


import com.alibaba.fastjson.JSONArray;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.pojo.GrantryTransVO;
import com.xxl.job.executor.pojo.OutListVO;
import com.xxl.job.executor.util.JsonUtil;

import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

public class DBUtils {

	/**
	 * 将需要的数据进行集合返回
	 * */
	public static List<Map<String,Object>> queryData(Connection conn, String sql) throws SQLException {

		if(sql==null || sql==""){
			return null;
		}
		//XxlJobLogger.log("execute sql>>>"+sql);
		//XxlJobLogger.log("----------------------------------");
		PreparedStatement preparedStatement =  null;
		preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		while(rs.next()){//判断是否还有下一行
			Map<String,Object> map = new HashMap<String, Object>();
			for (int i = 1; i <= count; i++) {
				map.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
//				XxlJobLogger.log(rsmd.getColumnName(i) + "---" + rs.getObject(rsmd.getColumnName(i)));
			}
			list.add(map);
		}
		rs.close();
		preparedStatement.close();
		return list;
	}

	public static String getCurSynRecordNum(Connection conn,String name) throws SQLException{
	    final String tableName = "TB_SYNC_RECORD";
		PreparedStatement preparedStatement =  null;
		StringBuffer stringBufferSql= new StringBuffer("select LAST_MAX_VALUE from ");
		stringBufferSql.append(tableName);
		stringBufferSql.append(" where TABLE_NAME='");
        stringBufferSql.append(name);
        stringBufferSql.append("'");
		//查询sql
		XxlJobLogger.log("execute sql>>>"+stringBufferSql.toString());
		preparedStatement = conn.prepareStatement(stringBufferSql.toString());
		ResultSet rs = preparedStatement.executeQuery();
		String recordNum =null;
		while(rs.next()){//判断是否还有下一行
            recordNum = rs.getString("LAST_MAX_VALUE");//获取数据库表中syn_time字段的值
		}
		rs.close();
		preparedStatement.close();
		return recordNum;
	}

	public static String getCurDate(Connection conn) throws SQLException {
		String sql = " select to_char(sysdate,'yyyy-mm-dd') as dateStr from dual ";
		
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		String recordNum =null;
		while(rs.next()){//判断是否还有下一行
            recordNum = rs.getString("dateStr");//获取数据库表中syn_time字段的值
		}
		rs.close();
		preparedStatement.close();
		return recordNum;
	}

	public static BigDecimal getDaysGap(Connection conn,String sql) throws SQLException{
		PreparedStatement preparedStatement =  null;
		//查询sql
		preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		BigDecimal recordNum =null;
		while(rs.next()){//判断是否还有下一行
			recordNum = rs.getBigDecimal("DAYS");//获取表中数量
		}
		rs.close();
		preparedStatement.close();
		return recordNum;
	}

	public static BigDecimal getTableCounts(Connection conn,String sql) throws SQLException{
		PreparedStatement preparedStatement =  null;
		//查询sql
		preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		BigDecimal recordNum =null;
		while(rs.next()){//判断是否还有下一行
			recordNum = rs.getBigDecimal("NUMCOUNTS");//获取表中数量
		}
		rs.close();
		preparedStatement.close();
		return recordNum;
	}

    //添加或者修改记录
    public static Integer modifySynRecordNum(Connection conn,String name,String oldRecord,String curRecord) throws SQLException{
	    Integer ret = 0;
        final String tableName = "TB_SYNC_RECORD";
        PreparedStatement preparedStatement =  null;
        StringBuffer stringBufferSql=new StringBuffer();

        stringBufferSql.append("update ").append(tableName).append(" set LAST_MAX_VALUE='");
        stringBufferSql.append(curRecord);
        stringBufferSql.append("',UPDATE_TIME=sysdate where TABLE_NAME='");
        stringBufferSql.append(name);
        stringBufferSql.append("' and LAST_MAX_VALUE='").append(oldRecord).append("'");

		XxlJobLogger.log("execute sql>>>"+stringBufferSql.toString());
        preparedStatement = conn.prepareStatement(stringBufferSql.toString());
        ret = preparedStatement.executeUpdate();
        preparedStatement.close();
        return ret;
    }

    public static BigDecimal getMaxRecordId(Connection conn,String tableName) throws SQLException{
        PreparedStatement preparedStatement =  null;
        StringBuffer stringBufferSql= new StringBuffer("select max(roadTimeFlagInt) as MAXID from ");
        stringBufferSql.append(tableName);
        //查询sql
        preparedStatement = conn.prepareStatement(stringBufferSql.toString());
        ResultSet rs = preparedStatement.executeQuery();
		BigDecimal maxId =null;
        while(rs.next()){//判断是否还有下一行
            maxId = rs.getBigDecimal("MAXID");//获取数据库表中syn_time字段的值
        }
		rs.close();
        preparedStatement.close();
        return maxId;
    }

	public static BigDecimal getMaxRecordIdByDataState(Connection conn,String tableName) throws SQLException{
		PreparedStatement preparedStatement =  null;
		StringBuffer stringBufferSql= new StringBuffer("select max(roadTimeFlagInt) as MAXID from ");
		stringBufferSql.append(tableName);
		stringBufferSql.append(" where DATASTATE=99");
		//查询sql
		preparedStatement = conn.prepareStatement(stringBufferSql.toString());
		ResultSet rs = preparedStatement.executeQuery();
		BigDecimal maxId =null;
		while(rs.next()){//判断是否还有下一行
			maxId = rs.getBigDecimal("MAXID");//获取数据库表中syn_time字段的值
		}
		rs.close();
		preparedStatement.close();
		return maxId;
	}

	//添加或者修改记录
	public static Integer execute(Connection conn, String sql) throws SQLException{
		//XxlJobLogger.log("execute sql>>>"+sql);
		Integer ret = 0;
		PreparedStatement preparedStatement =  null;
		preparedStatement = conn.prepareStatement(sql);
		ret = preparedStatement.executeUpdate();
		preparedStatement.close();
		return ret;
	}

	/**
	 *
	 * 标题：getCurSynRecordId
	 * 描述：获取同步后最大的时间
	 * 输入：@param conn 数据库连接
	 * 输入：@tableName  归集库数据进行同步的表名
	 * 输入：@colStr     查询数据库字段
	 * 返回：maxTimeStr  最新的同步时间
	 * 创建人：yzm
	 * 创建时间：2017-10-18上午9:24:39
	 */
	public static Integer getSeqNextVal(Connection conn,String seqName) throws SQLException{
		PreparedStatement preparedStatement =  null;
		StringBuffer stringBufferSql= new StringBuffer("select ");
		stringBufferSql.append(seqName);
		stringBufferSql.append(".nextval as seqVal from dual");
		//查询sql
		preparedStatement = conn.prepareStatement(stringBufferSql.toString());
		ResultSet rs = preparedStatement.executeQuery();
		Integer maxId =null;
		while(rs.next()){//判断是否还有下一行
			maxId = rs.getInt("seqVal");
		}
		rs.close();
		preparedStatement.close();
		return maxId;
	}

	/**
	 *
	 * 标题：getCurSynRecordId
	 * 描述：获取同步后最大的时间
	 * 输入：@param conn 数据库连接
	 * 输入：@tableName  归集库数据进行同步的表名
	 * 输入：@colStr     查询数据库字段
	 * 返回：maxTimeStr  最新的同步时间
	 * 创建人：yzm
	 * 创建时间：2017-10-18上午9:24:39
	 */
	public static String getDBCurTime(Connection conn) throws SQLException{
		PreparedStatement preparedStatement =  null;
		StringBuffer stringBufferSql= new StringBuffer("select to_char(sysdate - interval'10'minute,'yyyy-mm-dd hh24:mi:ss') as dateStr from dual");
		//查询sql
		preparedStatement = conn.prepareStatement(stringBufferSql.toString());
		ResultSet rs = preparedStatement.executeQuery();
		String dateStr =null;
		while(rs.next()){//判断是否还有下一行
			dateStr = rs.getString("dateStr");
		}
		rs.close();
		preparedStatement.close();
		if (dateStr == null) {
			ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
			dateStr = configProperties.getTimePoint();
		}
//		dateStr += ".000000000";//转Date类型不用加上毫秒数
		return dateStr;
	}

	public static String getDBCurTimeBefore3Min(Connection conn) throws SQLException{
		PreparedStatement preparedStatement =  null;
		StringBuffer stringBufferSql= new StringBuffer("select to_char(sysdate - interval'3'minute,'yyyy-mm-dd hh24:mi:ss') as dateStr from dual");
		//查询sql
		preparedStatement = conn.prepareStatement(stringBufferSql.toString());
		ResultSet rs = preparedStatement.executeQuery();
		String dateStr =null;
		while(rs.next()){//判断是否还有下一行
			dateStr = rs.getString("dateStr");
		}
		rs.close();
		preparedStatement.close();
		if (dateStr == null) {
			ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
			dateStr = configProperties.getTimePoint();
		}
//		dateStr += ".000000000";//转Date类型不用加上毫秒数
		return dateStr;
	}


    /**
     * 获取指定时间加10分钟后的时间
     * @param conn
     * @param lastMaxTime
     * @return
     * @throws SQLException
     */
    public static String getDBInc10MinTime(Connection conn,String lastMaxTime) throws SQLException{
        PreparedStatement preparedStatement =  null;
        StringBuffer stringBufferSql= new StringBuffer();
        stringBufferSql.append("select to_char(to_date('"+lastMaxTime+"','yyyy-MM-dd HH24:mi:ss') + interval'10'minute,'yyyy-mm-dd hh24:mi:ss')");
        stringBufferSql.append(" as dateStr from dual");
        preparedStatement = conn.prepareStatement(stringBufferSql.toString());
        ResultSet rs = preparedStatement.executeQuery();

        String dateStr =null;
        while(rs.next()){//判断是否还有下一行
            dateStr = rs.getString("dateStr");
        }
        rs.close();
        preparedStatement.close();
        if (dateStr == null) {
            ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
            dateStr = configProperties.getTimePoint();
        }
        return dateStr;
    }


    /**
     * 7天以前的数据
     * @param conn
     * @param lastMaxTime
     * @return
     * @throws SQLException
     */
   public static String getServenDayAgoData(Connection conn,String lastMaxTime)  throws SQLException{
	   PreparedStatement preparedStatement =  null;
       StringBuffer stringBufferSql= new StringBuffer();
       stringBufferSql.append("select to_char(to_date('"+lastMaxTime+"','yyyy-MM-dd HH24:mi:ss') + interval'-7'day,'yyyy-mm-dd hh24:mi:ss')");
       stringBufferSql.append(" as dateStr from dual");
       preparedStatement = conn.prepareStatement(stringBufferSql.toString());
       ResultSet rs = preparedStatement.executeQuery();

       String dateStr =null;
       while(rs.next()){//判断是否还有下一行
           dateStr = rs.getString("dateStr");
       }
       rs.close();
       preparedStatement.close();
       if (dateStr == null) {
           ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
           dateStr = configProperties.getTimePoint();
       }
       return dateStr;
   }
   
   /**
    * 10天以前的数据
    * @param conn
    * @param lastMaxTime
    * @return
    * @throws SQLException
    */
  public static String getTenDayAgoData(Connection conn,String lastMaxTime)  throws SQLException{
	   PreparedStatement preparedStatement =  null;
      StringBuffer stringBufferSql= new StringBuffer();
      stringBufferSql.append("select to_char(to_date('"+lastMaxTime+"','yyyy-MM-dd HH24:mi:ss') + interval'-9'day,'yyyy-mm-dd hh24:mi:ss')");
      stringBufferSql.append(" as dateStr from dual");
      preparedStatement = conn.prepareStatement(stringBufferSql.toString());
      ResultSet rs = preparedStatement.executeQuery();

      String dateStr =null;
      while(rs.next()){//判断是否还有下一行
          dateStr = rs.getString("dateStr");
      }
      rs.close();
      preparedStatement.close();
      if (dateStr == null) {
          ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
          dateStr = configProperties.getTimePoint();
      }
      return dateStr;
  }

   /**
    * 30天以前的数据
    * @param conn
    * @param lastMaxTime
    * @return
    * @throws SQLException
    */
  public static String getThirtyDayAgoData(Connection conn)  throws SQLException{
	   PreparedStatement preparedStatement =  null;
      StringBuffer stringBufferSql= new StringBuffer();
      stringBufferSql.append(" select to_char(sysdate + interval'-30'day,'yyyy-mm-dd')  as dateStr from dual ");     
      preparedStatement = conn.prepareStatement(stringBufferSql.toString());
      ResultSet rs = preparedStatement.executeQuery();

      String dateStr =null;
      while(rs.next()){//判断是否还有下一行
          dateStr = rs.getString("dateStr");
      }
      rs.close();
      preparedStatement.close();
      if (dateStr == null) {
          ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
          dateStr = configProperties.getTimePoint();
      }
      return dateStr;
  }

	/**
	 * 将clob类型字段值转换成String类型
	 * @param clob
	 * @return
	 */
	public static String clobToString(Clob clob) throws SQLException,IOException {
		if (clob == null) {
			return null;
		}
		Reader inStreamDoc = clob.getCharacterStream();
		char[] tempDoc = new char[(int) clob.length()];
		inStreamDoc.read(tempDoc);
		inStreamDoc.close();
		return new String(tempDoc);
	}

	/**
	 *
	 * 标题：getCurSynRecordId
	 * 描述：获取同步后最大的时间
	 * 输入：@param conn 数据库连接
	 * 输入：@tableName  归集库数据进行同步的表名
	 * 输入：@colStr     查询数据库字段
	 * 返回：maxTimeStr  最新的同步时间
	 * 创建人：yzm
	 * 创建时间：2017-10-18上午9:24:39
	 */
	public static String getTableMaxTime(Connection conn, String tableName, String columnName) throws SQLException{
		PreparedStatement preparedStatement =  null;
		String stringSql= "select to_char(max("+columnName+"),'yyyy-mm-dd hh24:mi:ss') as maxTime from "+tableName;
		//查询sql
		preparedStatement = conn.prepareStatement(stringSql);
		ResultSet rs = preparedStatement.executeQuery();
		String strTime =null;
		while(rs.next()){//判断是否还有下一行
			strTime = rs.getString("maxTime");
		}
		rs.close();
		preparedStatement.close();
//		if (dateStr == null) {
//			ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
//			dateStr = configProperties.getTimePoint();
//		}
		return strTime;
	}

	public static Map<String, Integer> statisJsonContentCnt(List<Map<String, Object>> list) throws Exception {
		Map<String, Integer> cntMap = new HashMap<>();
		ObjectMapper ob = JsonUtil.getObjectMapper();
		Iterator<Map<String,Object>> iterator = list.iterator();
		OutListVO entity = null;
		String strDate = "";
		while(iterator.hasNext()) {
			Map<String,Object> temp = iterator.next();
			String json = DBUtils.clobToString((Clob)temp.get("JSONCONTENT"));
			JSONArray dataArray = JSONArray.parseArray(json);
			for(int i=0,j=dataArray.size(); i<j; ++i) {
				String jsonTmp = dataArray.getString(i);
				entity = ob.readValue(jsonTmp, OutListVO.class);
				strDate = entity.getOpTime().substring(0,10);
				if (cntMap.containsKey(strDate)) {
					cntMap.put(strDate,cntMap.get(strDate) + 1);
				}
				else {
					cntMap.put(strDate, 1);
				}
			}
		}
		return cntMap;
	}

	public static Map<String, Integer> statisJsonGrantryContentCnt(List<Map<String, Object>> list) throws Exception {
		Map<String, Integer> cntMap = new HashMap<>();
		ObjectMapper ob = JsonUtil.getObjectMapper();
		Iterator<Map<String,Object>> iterator = list.iterator();
		GrantryTransVO entity = null;
		String strDate = "";
		while(iterator.hasNext()) {
			Map<String,Object> temp = iterator.next();
			String json = DBUtils.clobToString((Clob)temp.get("JSONCONTENT"));
			JSONArray dataArray = JSONArray.parseArray(json);
			for(int i=0,j=dataArray.size(); i<j; ++i) {
				String jsonTmp = dataArray.getString(i);
				entity = ob.readValue(jsonTmp, GrantryTransVO.class);
				strDate = entity.getTransTime().substring(0,10);
				if (cntMap.containsKey(strDate)) {
					cntMap.put(strDate,cntMap.get(strDate) + 1);
				}else {
					cntMap.put(strDate, 1);
				}
			}
		}
		return cntMap;
	}
}